'''
Created on 2020年3月3日

@author: Administrator
'''
from flask import json
from orm.model import User,Customer,MyCustomer,Dept,db
from view.baseview import BaseView
from view.mycustomerview import MyCustomerView
import time,datetime
from peewee import JOIN,fn
from common.public import *
from config import *
from pathlib import Path
import os,csv
import pandas as pd

class CustomerView(BaseView):
    def __init__(self):
        pass

    def query(self, params):
        """根据条件取得公共客户列表，params：条件参数"""
        result=[]
        assinedids = []
        total = 0
        rows = None
        where_condition = True

        if params["keyword"]:
            likestr="%" + params["keyword"] +"%"
            where_condition=(Customer.compname ** likestr )
            where_condition=where_condition | (Customer.legalperson** likestr )
            where_condition=where_condition | (Customer.address** likestr )
            where_condition=where_condition | (Customer.phone** likestr )
            where_condition=where_condition | (Customer.memo** likestr )
        # 取得部门管辖区域
        if params["userid"]:
            user = User.get(User.id == params["userid"])
            deptids = Dept.select(Dept.id).where(Dept.up_id==user.dept_id)
            if deptids:
                pass
            else:
                where_condition=where_condition &(Customer.dept_id==user.dept_id)
        if params["assignetype"]:
            where_condition=where_condition &(Customer.assignetype.in_(params["assignetype"]))
        if params["taxtype"]:
            where_condition = where_condition & (Customer.taxtype==(params["taxtype"]))
        if params["src"]:
            where_condition=where_condition &(Customer.src.in_(params["src"]))
        if params["founddate"]:
            likestr= params["founddate"] 
            where_condition=where_condition & ((Customer.founddate >= params["founddate"][0]) & (Customer.founddate <= params["founddate"][1]) )
        if params["createdatetime"]:
            likestr= params["createdatetime"] 
            where_condition=where_condition & ((Customer.createdatetime >= params["createdatetime"][0] + " 00:00:00") & (Customer.createdatetime <= params["createdatetime"][1] + " 23:59:59") )
        if params["status"]:
            where_conditionAssigned=(MyCustomer.status.in_([MYCUSTOMER_STATUS_UNSIGNED,MYCUSTOMER_STATUS_SIGNED,MYCUSTOMER_STATUS_VISITED,MYCUSTOMER_STATUS_TRACKED,MYCUSTOMER_STATUS_TRANSED]))
            if params["status"] == CUSTOMER_STATUS_ASSIGNED:
                # 已分配客户查询
                where_condition= where_condition & where_conditionAssigned
                total = Customer.select(fn.Distinct(Customer.id),Customer.compname,Customer.legalperson,Customer.registeredcapital,Customer.businessscope,Customer.founddate,Customer.area\
                                        ,Customer.address,Customer.contacter,Customer.phone,Customer.comptype,Customer.affiliation,Customer.createdatetime,Customer.updatetime,Customer.src\
                                        ,Customer.taxtype,Customer.assignetype,Customer.status,Customer.memo,Customer.dept_id)\
                    .join(MyCustomer, JOIN.RIGHT_OUTER, on=(Customer.id == MyCustomer.customer_id)).where(where_condition).count()
                rows = Customer.select(fn.Distinct(Customer.id),Customer.compname,Customer.legalperson,Customer.registeredcapital,Customer.businessscope,Customer.founddate,Customer.area\
                                        ,Customer.address,Customer.contacter,Customer.phone,Customer.comptype,Customer.affiliation,Customer.createdatetime,Customer.updatetime,Customer.src\
                                        ,Customer.taxtype,Customer.assignetype,Customer.status,Customer.memo,Customer.dept_id)\
                    .join(MyCustomer, JOIN.RIGHT_OUTER, on=(Customer.id == MyCustomer.customer_id)).where(where_condition).order_by(Customer.createdatetime.desc()).paginate(int(params["pageNo"]), int(params["pagesize"]))
            elif params["status"] ==CUSTOMER_STATUS_UNASSIGNED:
                # 未分配客户查询
                # 已分配的id
                assinedids =  Customer.select(MyCustomer.customer_id).join(MyCustomer, JOIN.RIGHT_OUTER, on=(Customer.id == MyCustomer.customer_id)).where(where_condition & where_conditionAssigned)
                print(assinedids)
                where_condition = where_condition & (Customer.id.not_in(assinedids)) & (Customer.status!=CUSTOMER_STATUS_FORBIDDEN)
                total = Customer.select().where(where_condition).count()
                rows = Customer.select().where(where_condition).order_by(Customer.createdatetime.desc()).paginate(int(params["pageNo"]), int(params["pagesize"]))
            elif params["status"] ==CUSTOMER_STATUS_FORBIDDEN:
                # 已禁用客户查询
                where_condition=where_condition &(Customer.status== CUSTOMER_STATUS_FORBIDDEN)
                total = Customer.select().where(where_condition).count()
                rows = Customer.select().where(where_condition).order_by(Customer.createdatetime.desc()).paginate(int(params["pageNo"]), int(params["pagesize"]))
        print(rows)
        for row in rows:
            data = {
                "id":row.id,
                "compname":row.compname,
                "legalperson":row.legalperson,
                "contacter":row.contacter,
                "area":row.area,
               "address":row.address,
               "phone":row.phone,
               "comptype":row.comptype,
               "registeredcapital":row.registeredcapital,
               "businessscope":row.businessscope,
               "founddate":str(row.founddate),
               "createdatetime":str(row.createdatetime),
               "assignetype":row.assignetype,
               "src":row.src,
                "taxtype": row.taxtype,
               "status":params["status"],
               "memo":row.memo
             }
            result.append(data )
        return super().resultSet(200, "", result,total=total)
    
         
    @db.atomic()                   
    def update(self, params):
        """
        更新
        """
        view = MyCustomerView()
        checkname = view.checkCompNameDistinct(params["id"],params["compname"])
        if checkname["code"] == 200:
            # 找到公共客户
            customer = Customer.get(Customer.id==params["id"])
            customer.compname=params["compname"]
            customer.legalperson=params["legalperson"]
            customer.contacter=params["contacter"]
            customer.registeredcapital=params["registeredcapital"]
            customer.businessscope=params["businessscope"]
            customer.founddate=params["founddate"]
            customer.comptype=params["comptype"]
            customer.updatetime=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
            customer.area = params["area"]
            customer.address=params["address"]
            customer.phone=params["phone"]
            customer.src=params["src"]
            customer.taxtype = params["taxtype"]
            customer.status=params["status"]
            customer.memo=params["memo"]
            customer.save()
            return super().resultSet(200, "更新成功", [])
        else:
            return checkname
        raise Exception("更新失败")
  
    @db.atomic()
    def add(self, params):
        """
        添加公共客户
        """
        """
        检查公司名称有没有重复
        """
        compnamefind = Customer.select().where(Customer.compname==params["compname"]).first()
        if not compnamefind:
            # 取得部门
            if params["userid"]:
                user = User.get(User.id == params["userid"])
            founddate = None
            if params["founddate"]:
                founddate = params["founddate"]
            Customer.create(
                compname=params["compname"],
                legalperson=params["legalperson"],
                contacter=params["contacter"],
                registeredcapital=params["registeredcapital"],
                businessscope=params["businessscope"],
                founddate=founddate,
                comptype=params["comptype"],
                area = params["area"],
                address=params["address"],
                phone=params["phone"],
                createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
                updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
                assignetype=CUSTOMER_ASSIGNETYPE_MANUAL,
                src=params["src"],
                status=params["status"],
                memo=params["memo"],
                dept_id=user.dept_id
                
                )
            return super().resultSet(200, "添加成功", [])
        else:
            return super().resultSet(400, "客户已存在，添加失败。", [])
        raise Exception("异常添加失败")
    
    @db.atomic()
    def assigne(self, param):
        """
        分配到我的客户
        """
        #计算截止日期
        cur_date_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        # 自动分配客户1day期限
        enddatetime_str = (datetime.datetime.now()+datetime.timedelta(days=SALE_TYPE_1DAY)).strftime("%Y-%m-%d %H:%M:%S")
        # 手动分配客户无分类4h期限
        enddatetime4h_str = (datetime.datetime.now()+datetime.timedelta(hours=SALE_TYPE_4HOUR)).strftime("%Y-%m-%d %H:%M:%S")
        # 手动分配客户线索客户1h期限
        enddatetime30m_str = (datetime.datetime.now()+datetime.timedelta(minutes=SALE_TYPE_30M)).strftime("%Y-%m-%d %H:%M:%S")
        # 取得跟踪者信息
        user = User.get(User.id == param["trackerid"])
        for id in param["ids"]:
            # 取得公共客户
            rec = Customer.get(Customer.id == id)
            # 将公共客户所属部门改为跟踪者部门ID
            rec.dept_id = user.dept_id
            rec.save()
            #查询是否自动
            if rec.src != CUSTOMER_SRC_XIANSUO and rec.assignetype == CUSTOMER_ASSIGNETYPE_MANUAL :
                enddatetime_str = enddatetime4h_str
            elif rec.src != CUSTOMER_SRC_XIANSUO and rec.assignetype == CUSTOMER_ASSIGNETYPE_AUTO :
                enddatetime_str = enddatetime_str
            elif rec.src== CUSTOMER_SRC_XIANSUO:
                enddatetime_str = enddatetime30m_str

            MyCustomer.create(
                customer_id = id,
                saletype=SALE_TYPE_NONE,
                weixin = MYCUSTOMER_WEIXIN_NONE,
                adddatetime=cur_date_str,
                enddatetime =enddatetime_str,
                tracker_id =param["trackerid"],
                status=MYCUSTOMER_STATUS_UNSIGNED
                )
        return super().resultSet(200, "分配成功", [])
        raise Exception("添加失败")

    @db.atomic()                   
    def importCustomer(self, request, src_userid):
        """
        导入客户
        """
        try:
            if request.method == 'POST' and 'file' in request.files:
                param = src_userid.split(",")
                assignetype = param[2]
                src = param[1]
                userid = param[0]
                # 取得部门
                deptid = param[3]
                #user = User.get(User.id == userid)

                
                file= request.files['file']
                path = UPLOAD_PATH
                filename = path + "/" +file.filename
                my_path = Path(path)
                if not my_path.exists():
                    # 指定的文件或目录不存在:
                    os.makedirs(path) 
                file.save(filename)

                data_xls = pd.read_excel(filename, index_col=0)
                data_xls.to_csv(filename + '.csv', encoding='utf-8')

                view = MyCustomerView()
                count = 0
                overnum = 0
                newnum = 0

                with open(filename + '.csv', encoding='utf-8') as csvfile:
                    reader = csv.reader(csvfile)
                    for param in reader:
                        if len(param) != 10:
                            return super().resultSet(400, "文件格式不正确！", [])
                        print(param)
                        if param[8]=='' or param[8]=='电话号码':
                            continue
                        count = count + 1
                        checkname = view.checkCompNameDistinct("",param[0])
                        checkphone = view.checkPhoneDistinct("",param[8])
                        if checkname["code"] != 200:
                            # 如果公司名称存在
                            overnum = overnum + 1
                        else:
                            # 如果公司名称不存在
                            textmemo = ""
                            status = CUSTOMER_STATUS_NORMAL
                            if checkphone["code"] != 200:
                                if param[8]:
                                    textmemo = "电话号码有重复"
                                    status = CUSTOMER_STATUS_FORBIDDEN
                            if param[6]=="":
                                founddate = None
                            else:
                                founddate = param[6]
                            if param[9]:
                                if param[8]!=param[9]:
                                    phone=str(param[8]).replace('.0','')+ "," + str(param[9]).replace('.0','')
                                else:
                                    phone=str(param[8]).replace('.0','')
                            else:
                                phone=str(param[8]).replace('.0','')
                            Customer.create(
                                compname=param[0],
                                legalperson=param[4],
                                registeredcapital=param[3],
                                businessscope=param[7],
                                founddate=founddate,
                                comptype=param[1],
                                area = param[2],
                                address=param[5],
                                phone=phone,
                                createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
                                updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
                                src=src,
                                assignetype=assignetype,
                                status=status,
                                memo=textmemo,
                                dept_id = deptid
                                )
                            newnum = newnum + 1

                    if count > 0:
                        return super().resultSet(200, "共" + str(count) + "个客户，新导入成功" + str(newnum) + "个，未导入重复客户" + str(overnum) + "个。", [])
                    else:
                        return super().resultSet(400, "数据重复，未导入数据！", [])

#                 #打开文件，获取excel文件的workbook（工作簿）对象
#                 excel = xlrd.open_workbook(filename,encoding_override="utf-8")
#
#                 #获取sheet对象
#                 all_sheet = excel.sheets()
#
#                 #循环遍历每个sheet对象
#                 sheet_name = []
#                 count = 0
#                 overnum = 0
#                 newnum = 0
#                 for sheet in all_sheet:
#                     sheet_name.append(sheet.name)
#                     print("正在导入" + sheet.name)
# #                     if sheet.nrows < 2 or sheet.ncols != 10 or sheet.ncols != 5 or sheet.ncols != 8:
# #                         return super().resultSet(400, sheet.name + "格式不正确，导入失败！", [])
#                     rowNum = 1
#                     for each_row in range(sheet.nrows):#循环打印每一行
#                         print("正在导入" + str(each_row))
# #                       if rowNum > 0:
#                         count = count + 1
#                         param = sheet.row_values(each_row)
#                         view = MyCustomerView()
#                         # 如果是10列格式 机构名称    类型    地区    注册资本    法定代表人    住所    成立日期    经营范围    电话号码 备注
#                         if sheet.ncols == 10 or sheet.ncols == 9:
#                             if param[8]=='' or param[8]=='电话':
#                                 continue
#                             checkname = view.checkCompNameDistinct("",param[0])
#                             checkphone = view.checkPhoneDistinct("",param[8])
#                             if checkname["code"] != 200:
#                                 # 如果公司名称存在
# #                                 # 备注
# #                                 textmemo = param[9]
# #                                 # 成立日期
# #                                 if param[6]=="":
# #                                     founddate = None
# #                                 else:
# #                                     founddate = param[6]
# #                                 cust = Customer.get(Customer.id==checkname["data"]["id"])
# #                                 cust.compname=param[0]
# #                                 cust.legalperson=param[4]
# #                                 cust.registeredcapital=param[3]
# #                                 cust.businessscope=param[7]
# #                                 cust.founddate=founddate
# #                                 cust.comptype=param[1]
# #                                 cust.area = param[2]
# #                                 cust.address=param[5]
# #                                 if param[9]:
# #                                     if param[8]!=param[9]:
# #                                         cust.phone=str(param[8]).replace('.0','')+ "," + str(param[9]).replace('.0','')
# #                                     else:
# #                                         cust.phone=str(param[8]).replace('.0','')
# #                                 else:
# #                                     cust.phone=str(param[8]).replace('.0','')
# #                                 cust.createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
# #                                 cust.updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
# #                                 cust.src=src
# #                                 cust.assignetype=assignetype
# #                                 cust.memo=textmemo
# #                                 cust.dept_id = deptid
# #                                 cust.save()
#                                 overnum = overnum + 1
#                             else:
#                                 # 如果公司名称不存在
#                                 textmemo = ""
#                                 status = CUSTOMER_STATUS_NORMAL
#                                 if checkphone["code"] != 200:
#                                     if param[8]:
#                                         textmemo = "电话号码有重复"
#                                         status = CUSTOMER_STATUS_FORBIDDEN
#                                 else:
#                                     if sheet.ncols == 9:
#                                         textmemo = ""
#                                     elif sheet.ncols == 10:
#                                         textmemo = param[9]
#                                 if param[6]=="":
#                                     founddate = None
#                                 else:
#                                     founddate = param[6]
#                                 if param[9]:
#                                     if param[8]!=param[9]:
#                                         phone=str(param[8]).replace('.0','')+ "," + str(param[9]).replace('.0','')
#                                     else:
#                                         phone=str(param[8]).replace('.0','')
#                                 else:
#                                     phone=str(param[8]).replace('.0','')
#                                 Customer.create(
#                                     compname=param[0],
#                                     legalperson=param[4],
#                                     registeredcapital=param[3],
#                                     businessscope=param[7],
#                                     founddate=founddate,
#                                     comptype=param[1],
#                                     area = param[2],
#                                     address=param[5],
#                                     phone=phone,
#                                     createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                     updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                     src=src,
#                                     assignetype=assignetype,
#                                     status=status,
#                                     memo=textmemo,
#                                     dept_id = deptid
#                                     )
#                                 newnum = newnum + 1
#                         elif sheet.ncols == 7:
#                             # 绍兴格式  公司名称，成立日期，电话，范围，证件号，地址，法人
#                             if param[2]=='':
#                                 continue
#                             checkname = view.checkCompNameDistinct("",param[0])
#                             checkphone = view.checkPhoneDistinct("",param[2])
#                             if checkname["code"] != 200:
#                                 # 如果公司名称存在
#                                 # 成立日期
# #                                 if param[1]=="":
# #                                     founddate = None
# #                                 else:
# #                                     founddate = param[1]
# #                                 cust = Customer.get(Customer.id==checkname["data"]["id"])
# #                                 cust.compname=param[0]
# #                                 cust.legalperson=param[6]
# #                                 cust.businessscope=param[3]
# #                                 cust.founddate=founddate
# #                                 cust.address = param[5]
# #                                 cust.phone = param[2]
# #                                 cust.createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
# #                                 cust.updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
# #                                 cust.src=src
# #                                 cust.assignetype=assignetype
# #                                 cust.dept_id = deptid
# #                                 cust.save()
#                                 overnum = overnum + 1
#                             else:
#                                 # 如果公司名称不存在
#                                 textmemo = ""
#                                 status = CUSTOMER_STATUS_NORMAL
#                                 if checkphone["code"] != 200:
#                                     if param[2]:
#                                         textmemo = "电话号码有重复"
#                                         status = CUSTOMER_STATUS_FORBIDDEN
#                                 if param[1]=="":
#                                     founddate = None
#                                 else:
#                                     founddate = param[1]
#                                 Customer.create(
#                                     compname=param[0],
#                                     legalperson=param[6],
#                                     registeredcapital='',
#                                     businessscope=param[3],
#                                     founddate=founddate,
#                                     comptype='',
#                                     area = param[5],
#                                     address='',
#                                     phone=param[2],
#                                     createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                     updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                     src=src,
#                                     assignetype=assignetype,
#                                     status=status,
#                                     memo='',
#                                     dept_id = deptid
#                                     )
#                                 newnum = newnum + 1
#                         elif sheet.ncols == 6:
#                             # 如果是5列格式 公司名称    注册时间    联系电话    法定代表人    地址 备注
#                             if param[2]=='':
#                                 continue
#                             checkname = view.checkCompNameDistinct("",param[0])
#                             checkphone = view.checkPhoneDistinct("",param[2])
#                             if checkname["code"] != 200:
#                                 overnum = overnum + 1
#                                 continue
#                             textmemo = ""
#                             status = CUSTOMER_STATUS_NORMAL
#                             if checkphone["code"] != 200:
#                                 if param[2]:
#                                     textmemo = "电话号码有重复"
#                                     status = CUSTOMER_STATUS_FORBIDDEN
#                             else:
#                                 textmemo = param[5]
#                             if param[1]=="":
#                                 founddate = None
#                             else:
#                                 founddate = param[1]
#                             Customer.create(
#                                 compname=param[0],
#                                 legalperson=param[3],
#                                 registeredcapital="",
#                                 businessscope="",
#                                 founddate=founddate,
#                                 comptype="",
#                                 area = "",
#                                 address=param[4],
#                                 phone=param[2],
#                                 createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                 updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                 src=src,
#                                 assignetype=assignetype,
#                                 status=status,
#                                 memo=textmemo,
#                                 dept_id = deptid
#                                 )
#                             newnum = newnum + 1
#                         elif sheet.ncols == 4:
#                             # 如果是5列格式 公司名称   法定代表人   地址    联系电话
#                             if param[3]=='':
#                                 continue
#                             checkname = view.checkCompNameDistinct("",param[0])
#                             checkphone = view.checkPhoneDistinct("",param[2])
#                             if checkname["code"] != 200:
#                                 overnum = overnum + 1
#                                 continue
#                             textmemo = ""
#                             status = CUSTOMER_STATUS_NORMAL
#                             if checkphone["code"] != 200:
#                                 if param[3]:
#                                     textmemo = "电话号码有重复"
#                                     status = CUSTOMER_STATUS_FORBIDDEN
#                             Customer.create(
#                                 compname=param[0],
#                                 legalperson=param[1],
#                                 registeredcapital="",
#                                 businessscope="",
#                                 comptype="",
#                                 area = "",
#                                 address=param[2],
#                                 phone=param[3],
#                                 createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                 updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                 src=src,
#                                 assignetype=assignetype,
#                                 status=status,
#                                 memo=textmemo,
#                                 dept_id = deptid
#                                 )
#                             newnum = newnum + 1
#                         elif sheet.ncols == 8:
#                             # 如果是5列格式 公司名称    法定代表人    地址    联系电话    一行两个客户
#                             if param[3]=='':
#                                 #电话是空
#                                 continue
#                             checkname = view.checkCompNameDistinct("",param[0])
#                             checkphone = view.checkPhoneDistinct("",param[3])
#                             if checkname["code"] != 200:
#                                 overnum = overnum + 1
#                                 continue
#                             textmemo = ""
#                             status = CUSTOMER_STATUS_NORMAL
#                             if checkphone["code"] != 200:
#                                 if param[3]:
#                                     textmemo = "电话号码有重复"
#                                     status = CUSTOMER_STATUS_FORBIDDEN
#                             else:
#                                 textmemo = ""
#                             #注册日期
#                             founddate = None
#                             Customer.create(
#                                 compname=param[0],
#                                 legalperson=param[1],
#                                 registeredcapital="",
#                                 businessscope="",
#                                 founddate=founddate,
#                                 comptype="",
#                                 area = "",
#                                 address=param[2],
#                                 phone=param[3],
#                                 createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                 updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                 src=src,
#                                 assignetype=assignetype,
#                                 status=status,
#                                 memo=textmemo,
#                                 dept_id = deptid
#                                 )
#                             Customer.create(
#                                 compname=param[4],
#                                 legalperson=param[5],
#                                 registeredcapital="",
#                                 businessscope="",
#                                 founddate=founddate,
#                                 comptype="",
#                                 area = "",
#                                 address=param[6],
#                                 phone=param[7],
#                                 createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                 updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                 src=src,
#                                 assignetype=assignetype,
#                                 status=status,
#                                 memo=textmemo,
#                                 dept_id = deptid
#                                 )
#                             newnum = newnum + 1
#                         # 如果是企查查企业名称    登记状态    法定代表人    注册资本    成立日期    核准日期    所属省份    所属城市    所属区县    电话    更多电话    邮箱    更多邮箱    统一社会信用代码    纳税人识别号    注册号    组织机构代码    参保人数    企业类型    所属行业    曾用名    官网    企业地址    经营范围
#                         elif sheet.ncols == 24 :
#                             # 电话是空
#                             phone = ''
#                             if param[9]=='' and param[10]=='':
#                                 continue
#                             else:
#                                 if param[10]:
#                                     phone = str(param[9]) +',' + str(param[10])
#                                 else:
#                                     phone = str(param[9])
#                             checkname = view.checkCompNameDistinct("",param[0])
#                             checkphone = view.checkPhoneDistinct("",phone)
#                             if checkname["code"] != 200:
#                                 # 如果公司名称存在
#                                 # 成立日期
# #                                 if param[4]=="":
# #                                     founddate = None
# #                                 else:
# #                                     founddate = param[4]
# #                                 cust = Customer.get(Customer.id==checkname["data"]["id"])
# #                                 cust.compname=param[0]
# #                                 cust.legalperson=param[2]
# #                                 cust.registeredcapital=param[3]
# #                                 cust.businessscope=param[23]
# #                                 cust.founddate=founddate
# #                                 cust.comptype=param[18]
# #                                 cust.area = param[7]
# #                                 cust.address=param[22]
# #                                 cust.phone=phone
# #                                 cust.createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
# #                                 cust.updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
# #                                 cust.src=src
# #                                 cust.assignetype=assignetype
# #                                 cust.dept_id = deptid
# #                                 cust.save()
#                                 overnum = overnum + 1
#                             else:
#                                 textmemo = ""
#                                 status = CUSTOMER_STATUS_NORMAL
#                                 if checkphone["code"] != 200:
#                                     if phone:
#                                         textmemo = "电话号码有重复"
#                                         status = CUSTOMER_STATUS_FORBIDDEN
#                                 if param[4]=="":
#                                     founddate = None
#                                 else:
#                                     founddate = param[4]
#                                 Customer.create(
#                                     compname=param[0],
#                                     legalperson=param[2],
#                                     registeredcapital=param[3],
#                                     businessscope=param[23],
#                                     founddate=founddate,
#                                     comptype=param[18],
#                                     area = param[7],
#                                     address=param[22],
#                                     phone=phone,
#                                     createdatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                     updatetime =time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
#                                     src=src,
#                                     assignetype=assignetype,
#                                     status=status,
#                                     memo=textmemo,
#                                     dept_id = deptid
#                                     )
#                                 newnum = newnum + 1
#                         else:
#                             return super().resultSet(400, "数据不正确，导入失败！", [])
#                         rowNum = rowNum +1
#         #                     print("当前为%s行："% each_row,type(each_row))
#         #                     print(sheet.row_values(each_row),type(sheet.row_values(each_row)))
#         #                     first_row_value = sheet.row_values(0)#打印指定的某一行
#         #                     print("第一行的数据为:%s" % first_row_value)
#
#                 if count > 0:
#                     return super().resultSet(200, "共" + str(count) + "个客户，新导入成功" + str(newnum) + "个，重复客户" + str(overnum) + "个。", [])
#                 else:
#                     return super().resultSet(400, "数据重复，导入失败！", [])
#             else:
#                 return super().resultSet(400, "数据不正确，导入失败！", [])
        except IOError:
            return super().resultSet(400, "数据不正确，导入失败！", [])
        raise Exception("导入失败")
    
    @db.atomic()                   
    def delete(self, ids):
        """
        删除
        """
        userdel = Customer.delete().where(Customer.id.in_(ids)).execute()
        if userdel>=0:
            return super().resultSet(200, "删除成功", {})
        return super().resultSet(403, "删除失败", {})
        raise Exception("删除失败")

    def getTaxTyps(self):
        """取得现有数据中的税务类型"""
        result = []
        rows = Customer.select(fn.Distinct(Customer.taxtype))
        print(rows)
        for row in rows:
            result.append(row.taxtype)
        return super().resultSet(200, "", result)


if __name__ == '__main__':
    rows = Customer.select(Customer.id).where(Customer.founddate=='0000-00-00')
    for row in rows:
        cus = Customer.get(Customer.id == row.id)
        cus.founddate = None
        cus.save()

 